--select * from sys."V$RLOGFILE"
--alter DATABASE add LOGFILE '/dm8/data/DAMENG/DAMENG' size 256
--alter database RESIZE logfile '/dm8/data/DAMENG/DAMENG03.log'


--select "V$DATABASE".ARCH_MODE from SYS."V$DATABASE" 
--backup DATABASE
/*alter DATABASE MOUNT;
alter DATABASE ARCHIVELOG;
alter DATABASE NOARCHIVELOG;
alter DATABASE ADD ARCHIVELOG 'type=local,dest=/dm8/arch,file_size=128 , space_limit=10240';
alter DATABASE OPEN
*/

select * from SYS."V$DATABASE";
BACKUP DATABASE;
BACKUP TABLESPACE DMOA to DMOA0612 BACKUPSET '/dm8/backup/full/DMOA0612';
BACKUP TABLE OA.T_STUSCORE TO T_STUSCORE0612 BACKUPSET '/dm8/backup/full/T_STUSCORE0612';



-------------------------------------------------------------------------
--1.
BACKUP TABLESPACE DMOA TO DMOATASBAK BACKUPSET '/dm8/backup/DMOATBSBAK';

--2库级备份
BACKUP DATABASE TO OADBBAK BACKUPSET '/dm8/backup/OADBBAK'; 

--3
select * from T_STUSCORE;
/**
查看表空间
**/
SELECT * FROM SYS.USER_SEGMENTS t WHERE t.SEGMENT_NAME='T_STUSCORE';

--4模拟插入数据
truncate TABLE T_STUSCORE;
INSERT INTO T_STUSCORE( T_STUSCORE.SUBJECTID,T_STUSCORE.STUID,T_STUSCORE.SCORE) 
SELECT MOD(level,5)+1 as SUBJECTID , ROUND(level/5)+1 as STUID,FLOOR(DBMS_RANDOM.VALUE(150,50)) AS score
from DUAL CONNECT BY ROWNUM <=100000;
commit;

select * from T_STUSCORE;

select * from SYS."V$IFUN"  WHERE  "V$IFUN".NAME LIKE '%SYS%%CHECK%'
sp_file_sys_check
select * from T_STUSCORE;

--dmrman中运行
/*restore database '/dm8/dmdbms/data/DAMENG/dm.ini' tablespace DMOA from backupset '/dm8/backup/DMOATBSBAK';
recover database '/dm8/dmdbms/data/DAMENG/dm.ini' tablespace DMOA;*/


select * from T_STUSCORE;




----5.新建一个dmtest用户，将oa用户下的表和数据都导入到DMTEST用户中，使用逻辑导入
create USER DMTEST IDENTIFIED BY shyx39423737 DEFAULT TABLESPACE DMOA;

--dexp userid=OA/shyx39423737@192.168.188.136:5236 directory=/dm8/backup/dexp/ file=OADATA.dmp log=OAdexp.log  owner=OA charset=PG_UTF8

--
/*grant create table to dmtest;
grant create INDEX to dmtest;
grant create view to dmtest;
grant soi to dmtest;*/
--dimp userid=dmtest/shyx39423737@DM directory=/dm8/backup/dexp file=OADATA.dmp log=dmtestdimp.log owner=OA remap_schema=OA:dmtest

--6

--7

--8
/*select * from oa.T_STUSCORE;
truncate TABLE oa.T_STUSCORE;*/
/*INSERT INTO oa.T_STUSCORE( T_STUSCORE.SUBJECTID,T_STUSCORE.STUID,T_STUSCORE.SCORE) 
SELECT MOD(level,5)+1 as SUBJECTID , ROUND(level/5)+1 as STUID,FLOOR(DBMS_RANDOM.VALUE(150,50)) AS score
from DUAL CONNECT BY ROWNUM <=100000;*/
commit;


--9
/*dbms_stats.gather_table_stats('OA','T_STUSCORE');
dbms_stats.table_stats_show('OA','T_STUSCORE');
dbms_stats.column_stats_show('OA','T_STUSCORE','STUID');*/



---10

/*create or REPLACE PROCEDURE OA.P_GET_SUHECRAVGSCORE (SUBJECTID in T_STUSCORE.SUBJECTID%TYPE  ) 
AS
	cur CURSOR FOR select sysdate;
	x int :=1;
	v_STUID T_STUSCORE.STUID%TYPE;
	v_SCORE T_STUSCORE.SCORE%TYPE;
BEGIN
	OPEN cur for select t.STUID,t.SCORE from OA.T_STUSCORE t where t.SUBJECTID=SUBJECTID 
		order by t.score DESC;
	FETCH cur INTO v_STUID,v_SCORE;
	
	while cur%FOUND and x<=10 Loop
		print(v_STUID||':'||v_SCORE);
		FETCH cur INTO v_STUID,v_SCORE;
		x := x+1;
	end Loop;
	Close cur;

End;
	


OA.P_GET_SUHECRAVGSCORE(2)
*/
/*

create or REPLACE PROCEDURE OA.P_GET_SUHECRAVGSCOR2 (SUBJECTID in T_STUSCORE.SUBJECTID%TYPE  ) 
AS
	cur CURSOR for  select t.STUID,t.SCORE from OA.T_STUSCORE t where t.SUBJECTID=SUBJECTID 
		order by t.score DESC;
	x int :=1;
	v_STUID T_STUSCORE.STUID%TYPE;
	v_SCORE T_STUSCORE.SCORE%TYPE;
BEGIN
	OPEN cur;
	FETCH cur INTO v_STUID,v_SCORE;
	
	while cur%FOUND and x<=10 Loop
		print(v_STUID||':'||v_SCORE);
		FETCH cur INTO v_STUID,v_SCORE;
		x := x+1;
	end Loop;
	Close cur;

End;


OA.P_GET_SUHECRAVGSCOR2(2);
*/


--11
/*
CREATE OR REPLACE PROCEDURE F_GET_SUJECTAVGSCORE(C_SUBJECTID in T_STUSCORE.SUBJECTID%TYPE) 
AS
  V_AVGSCORE DECIMAL(5,2) ;
 
begin
	
	SELECT AVG(SCORE) into V_AVGSCORE  from oa.T_STUSCORE WHERE T_STUSCORE.SUBJECTID=C_SUBJECTID
 	GROUP BY T_STUSCORE.SUBJECTID;
 	
 	print('学科'||C_SUBJECTID ||'的平均成绩是: '||V_AVGSCORE);

end;

call F_GET_SUJECTAVGSCORE(1);
	
SELECT AVG(T_STUSCORE.SCORE)   from oa.T_STUSCORE WHERE T_STUSCORE.SUBJECTID=3
 	GROUP BY T_STUSCORE.SUBJECTID;

commit*/

/*create table oa.t_EMPIPDATESAL(
EMPLOYEE_ID INT,
EMPLOYEE_NAME varchar(30),
EMPLOYEE_NUM varchar(30),
department_di int,
oldsal int,
newsal int);

create or REPLACE TRIGGER tr_UPDATEEMPSAL
before update on t_EMP
BEGIN 
	IF(:new)*/



--3

/*

CREATE TABLE "OA"."T_UPDATESCORELOG"
(
"SUBJECTID" INT,
"STUID" INT,
"OLD_SCORE" INT ,
"NEW_SCORE" INT);


CREATE or REPLACE TRIGGER TR_UPDATESCORE 
	BEFORE UPDATE ON OA.T_STUSCORE for EACH ROW 
	BEGIN
		if(:new.SCORE<>:old.SCORE) THEN
			INSERT INTO T_UPDATESCORELOG(SUBJECTID,STUID,OLD_SCORE,NEW_SCORE) 
			VALUES (:new.SUBJECTID,:new.STUID,:old.SCORE,:new.SCORE);
		END if;
		
	END;
	
select * from T_STUSCORE;
update T_STUSCORE SET T_STUSCORE.SCORE=131 where T_STUSCORE.SUBJECTID=2 and T_STUSCORE.STUID=1;
select * from T_UPDATESCORELOG;*/


--4
/*CREATE TABLE T_TRUNCATELO(
operate_user VARCHAR(20),
operate_object_name VARCHAR(20),
operate_action_name VARCHAR(20)
);


CREATE OR REPLACE TRIGGER TR_TRUNCATELOG AFTER TRUNCATE or DROP ON SCHEMA 
BEGIN
	INSERT INTO T_TRUNCATELO(operate_user,operate_object_name,operate_action_name) VALUES(USER,ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE);

END;

select * from T_SUBJECT;
truncate table T_SUBJECT;
select * from T_TRUNCATELO;
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(1,'语文');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(2,'数学');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(3,'英语');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(4,'理科综合');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(5,'文科综合');*/






